package com.huike.pmps.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.huike.pmps.dao.POCourseSupplierDetailDao;
import com.huike.pmps.dao.PODao;
import com.huike.pmps.dao.SupplierSettlementDetailDao;
import com.huike.pmps.model.constants.GlobalConstants;
import com.huike.pmps.model.dto.common.PageInfo;
import com.huike.pmps.model.entity.Member;
import com.huike.pmps.model.entity.PO;
import com.huike.pmps.model.entity.POCourseSupplierDetail;
import com.huike.pmps.model.entity.SupplierSettlementDetail;
import com.huike.pmps.model.vo.PageResult;
import com.huike.pmps.model.vo.Result;
import com.huike.pmps.model.vo.supplier.SupplierPoVo;
import com.huike.pmps.model.vo.supplier.SupplierSettlementPoVo;
import com.huike.pmps.service.POCourseSupplierDetailService;
import com.huike.pmps.service.POService;
import com.huike.pmps.service.SupplierSettlementDetailService;
import com.huike.pmps.service.SupplierWarehouseReceiptService;
import io.swagger.models.auth.In;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 作者：谌贵斌(黑桃K)
 * 日期: 2888-08-08
 * 老师QQ: 272488352
 * 官方QQ交流群: 377748272
 * 刀客程序员官网：http://www.daoke360.com
 * 刀客程序员淘宝旗舰店：https://daoke360.taobao.com/
 */
@Service
@Slf4j
public class SupplierSettlementDetailServiceImpl implements SupplierSettlementDetailService {

    @Autowired
    PODao poDao;

    @Autowired
    POCourseSupplierDetailService poCourseSupplierDetailService;

    @Autowired
    POCourseSupplierDetailDao poCourseSupplierDetailDao;

    @Autowired
    SupplierSettlementDetailDao supplierSettlementDetailDao;
    @Autowired
    SupplierWarehouseReceiptService supplierWarehouseReceiptService;

    /**
     * 生成供应商结算单
     *
     * @param ids
     * @return
     */
    @Override
    public Result insertSupplierSettlementDetail(String ids) {
        String[] items = ids.split(",");
        SupplierSettlementDetail supplierSettlementDetail = new SupplierSettlementDetail();
        POCourseSupplierDetail poCourseSupplierDetail = null;
        for (String id : items) {
            poCourseSupplierDetail = poCourseSupplierDetailDao.selectById(Integer.valueOf(id));
            if (poCourseSupplierDetail != null) {
                BeanUtils.copyProperties(poCourseSupplierDetail, supplierSettlementDetail, new String[]{"id", "rowEdit", "updateTime", "createTime", "state"});
                PO po = poDao.selectById(poCourseSupplierDetail.getPoId());
                if (po != null)
                    supplierSettlementDetail.setPoContractAmount(po.getContractAmount());
                supplierSettlementDetail.setUpdateTime(new Date());
                supplierSettlementDetail.setCreateTime(new Date());
                supplierSettlementDetail.setPoCourseSupplierId(Integer.valueOf(id));

                supplierSettlementDetailDao.insert(supplierSettlementDetail);
                //修改课程-供应商 记录状态为 1 (生成了结算清单)
                poCourseSupplierDetail.setState(GlobalConstants.PO_COURSE_SUPPLIER_STATE_JSD);
                poCourseSupplierDetailDao.updateById(poCourseSupplierDetail);
            }
        }
        return Result.success();
    }

    @Override
    public Result selectOneByPOCourseSupplierId(Integer poCourseSupplierId) {
        QueryWrapper<SupplierSettlementDetail> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("po_course_supplier_id", poCourseSupplierId);
        return Result.success(supplierSettlementDetailDao.selectOne(queryWrapper));
    }

    /**
     * 删除结算清单
     *
     * @param id
     * @return
     */
    @Override
    public Result deleteById(Integer id) {
        Result result = supplierWarehouseReceiptService.selectOneBySupplierSettlementId(id);
        if (result.getData() != null) return Result.failure("请先删除入库单");
        SupplierSettlementDetail supplierSettlementDetail = supplierSettlementDetailDao.selectById(id);
        //删除课程-供应商结算清单
        if (supplierSettlementDetailDao.deleteById(id) > 0) {
            //更新课程-供应商 记录状态(0初始状态、1已生成结算清单)
            POCourseSupplierDetail poCourseSupplierDetail = POCourseSupplierDetail.builder()
                    .id(supplierSettlementDetail.getPoCourseSupplierId())
                    .state(GlobalConstants.PO_COURSE_SUPPLIER_STATE_INIT)
                    .build();

            poCourseSupplierDetailDao.updateById(poCourseSupplierDetail);
            return Result.success();
        } else {
            return Result.failure("没有可删除的数据");
        }
    }

    @Override
    public Result updateById(SupplierSettlementDetail supplierSettlementDetail) {
        supplierSettlementDetail.setRowEdit(false);
        if (supplierSettlementDetail.getEstimatedGenerationCost() != null && supplierSettlementDetail.getSupplierPaidAmount() != null) {
            double d1 = supplierSettlementDetail.getEstimatedGenerationCost().doubleValue();
            double d2 = supplierSettlementDetail.getSupplierPaidAmount().doubleValue();
            supplierSettlementDetail.setSupplierUnpaidAmount(new BigDecimal(d1 - d2));
        }
        if (supplierSettlementDetailDao.updateById(supplierSettlementDetail) > 0) {
            if (supplierSettlementDetail.getEstimatedGenerationCost() != null && supplierSettlementDetail.getSupplierPaidAmount() != null) {
                //更新 PO课程已支付 和 课程供应商已支付金额
                supplierSettlementDetailDao.updatePOCourseAndCourseSupplierPaidAmount(supplierSettlementDetail.getPoCourseId(), supplierSettlementDetail.getPoCourseSupplierId());
            }
            return Result.success();
        } else {
            return Result.failure("没有可更新的数据");
        }
    }

    /**
     * 结算单列表
     *
     * @param pageInfo
     * @return
     */
    @Override
    public Result selectByCondition(Member member, PageInfo pageInfo, String po, String customerName, String regionName, String provinceName, String courseName, String courseChargeName, String supplierName, Integer poCourseId, String paymentStateName, String taxPoint, String state) {
        QueryWrapper<SupplierSettlementDetail> queryWrapper = new QueryWrapper<>();


        if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_ADMIN)) {//管理员
        } else if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_REGION)) {//战区司令
            if (member.getRegionId() == null) return Result.failure("您的账号没有配置所属战区，请联系 系统管理员 进行战区配置");
            queryWrapper.eq("region_id", member.getRegionId());
        } else {
            if (member.getProvinceId() == null) return Result.failure("您的账号没有配置所属省区，请联系 系统管理员 进行省区配置");
            queryWrapper.eq("province_id", member.getProvinceId());
        }


        if (StringUtils.isNotBlank(po))
            queryWrapper.like("po", po);

        if (poCourseId != null)
            queryWrapper.eq("po_course_id", poCourseId);

        if (StringUtils.isNotBlank(customerName))
            queryWrapper.like("customer_name", customerName);

        if (StringUtils.isNotBlank(regionName))
            queryWrapper.like("region_name", regionName);

        if (StringUtils.isNotBlank(provinceName))
            queryWrapper.like("province_name", provinceName);

        if (StringUtils.isNotBlank(courseName))
            queryWrapper.like("course_name", courseName);

        if (StringUtils.isNotBlank(courseChargeName))
            queryWrapper.like("course_charge_name", courseChargeName);

        if (StringUtils.isNotBlank(supplierName))
            queryWrapper.like("supplier_name", supplierName);

        if (StringUtils.isNotBlank(paymentStateName))
            queryWrapper.like("payment_state_name", paymentStateName);

        if (StringUtils.isNotBlank(taxPoint))
            queryWrapper.like("tax_point", taxPoint);
        //20211009,陈异：分类查询结算单状态
        if (StringUtils.isNotBlank(state)){
            String[] states = state.split(",");
            queryWrapper.in("state", states);
        }

        queryWrapper.orderByDesc("id");
        Page<SupplierSettlementDetail> page = new Page<>(pageInfo.getPage(), pageInfo.getPageSize());
        Page<SupplierSettlementDetail> pageList = supplierSettlementDetailDao.selectPage(page, queryWrapper);
        if (pageList.getSize() > 0) {
            PageResult pageResult = PageResult.builder().pageNumber(pageList.getCurrent()).pageSize(pageList.getSize())
                    .total(pageList.getTotal()).rows(pageList.getRecords()).build();
            return Result.success(pageResult);
        } else {
            return Result.success("没有符合条件的记录");
        }
    }

    @Override
    public void exportSupplierSettlement(HttpServletResponse response, String po, String customerName, String regionName, String provinceName, String courseName, String courseChargeName, String supplierName, Member member, String paymentStateName, String taxPoint) {
        QueryWrapper<SupplierSettlementDetail> queryWrapper = new QueryWrapper<>();

        if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_ADMIN)) {//管理员
        } else if (member.getRolesIds().contains(GlobalConstants.SYSTEM_ROLE_REGION)) {//战区司令
            queryWrapper.eq("region_id", member.getRegionId());
        } else {
            queryWrapper.eq("province_id", member.getProvinceId());
        }


        if (StringUtils.isNotBlank(po))
            queryWrapper.like("po", po);

        if (StringUtils.isNotBlank(customerName))
            queryWrapper.like("customer_name", customerName);

        if (StringUtils.isNotBlank(regionName))
            queryWrapper.like("region_name", regionName);

        if (StringUtils.isNotBlank(provinceName))
            queryWrapper.like("province_name", provinceName);

        if (StringUtils.isNotBlank(courseName))
            queryWrapper.like("course_name", courseName);

        if (StringUtils.isNotBlank(courseChargeName))
            queryWrapper.like("course_charge_name", courseChargeName);

        if (StringUtils.isNotBlank(supplierName))
            queryWrapper.like("supplier_name", supplierName);

        if (StringUtils.isNotBlank(paymentStateName))
            queryWrapper.like("payment_state_name", paymentStateName);

        if (StringUtils.isNotBlank(taxPoint))
            queryWrapper.like("tax_point", taxPoint);

        List<SupplierSettlementDetail> supplierSettlementDetailList = supplierSettlementDetailDao.selectList(queryWrapper);
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("export_excel/supplier_settlement_template.xlsx");
        XSSFWorkbook xssfWorkbook = null;
        try {
            xssfWorkbook = new XSSFWorkbook(in);
            //获取第一个工作表sheet
            XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
            for (int i = 0; i < supplierSettlementDetailList.size(); i++) {
                SupplierSettlementDetail supplierSettlementDetail = supplierSettlementDetailList.get(i);
                XSSFRow row = sheetAt.createRow(i + 1);

                //po单号
                XSSFCell poCell = row.createCell(0);
                poCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(supplierSettlementDetail.getPo()))
                    poCell.setCellValue(supplierSettlementDetail.getPo());

                //课程ID
                XSSFCell courseIdCell = row.createCell(1);
                courseIdCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getCourseId() != null)
                    courseIdCell.setCellValue(supplierSettlementDetail.getCourseId());

                //课程名称
                XSSFCell courseNameCell = row.createCell(2);
                courseNameCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getCourseName() != null)
                    courseNameCell.setCellValue(supplierSettlementDetail.getCourseName());

                //课程经理/课程编导
                XSSFCell courseChargeNameCell = row.createCell(3);
                courseChargeNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(supplierSettlementDetail.getCourseChargeName()))
                    courseChargeNameCell.setCellValue(supplierSettlementDetail.getCourseChargeName());

                //供应商类型名称
                XSSFCell supplierTypeNameCell = row.createCell(4);
                supplierTypeNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(supplierSettlementDetail.getSupplierTypeName()))
                    supplierTypeNameCell.setCellValue(supplierSettlementDetail.getSupplierTypeName());

                //供应商名称
                XSSFCell supplierNameCell = row.createCell(5);
                supplierNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(supplierSettlementDetail.getSupplierName()))
                    supplierNameCell.setCellValue(supplierSettlementDetail.getSupplierName());


                //客户名称
                XSSFCell customerNameCell = row.createCell(6);
                customerNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(supplierSettlementDetail.getCustomerName()))
                    customerNameCell.setCellValue(supplierSettlementDetail.getCustomerName());

                //po对应的合同金额
                XSSFCell poContractAmountCell = row.createCell(7);
                poContractAmountCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getPoContractAmount() != null)
                    poContractAmountCell.setCellValue(supplierSettlementDetail.getPoContractAmount().toString());

                //服务类型名称
                XSSFCell serviceTypeNameCell = row.createCell(8);
                serviceTypeNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(supplierSettlementDetail.getServiceTypeName()))
                    serviceTypeNameCell.setCellValue(supplierSettlementDetail.getServiceTypeName());

                //已交付时长
                XSSFCell contractFinishedTimeLengthCell = row.createCell(9);
                contractFinishedTimeLengthCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getContractFinishedTimeLength() != null)
                    contractFinishedTimeLengthCell.setCellValue(supplierSettlementDetail.getContractFinishedTimeLength().toString());

                //单价
                XSSFCell unitPriceCell = row.createCell(10);
                unitPriceCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getUnitPrice() != null)
                    unitPriceCell.setCellValue(supplierSettlementDetail.getUnitPrice().toString());

                //数量
                XSSFCell quantityCell = row.createCell(11);
                quantityCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getQuantity() != null)
                    quantityCell.setCellValue(supplierSettlementDetail.getQuantity());


                //预估成本
                XSSFCell estimatedGenerationCostCell = row.createCell(12);
                estimatedGenerationCostCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getEstimatedGenerationCost() != null)
                    estimatedGenerationCostCell.setCellValue(supplierSettlementDetail.getEstimatedGenerationCost().toString());

                //已支付
                XSSFCell supplierPaidAmountCell = row.createCell(13);
                supplierPaidAmountCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getSupplierPaidAmount() != null)
                    supplierPaidAmountCell.setCellValue(supplierSettlementDetail.getSupplierPaidAmount().toString());

                //未支付
                XSSFCell supplierUnpaidAmountCell = row.createCell(14);
                supplierUnpaidAmountCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getSupplierUnpaidAmount() != null)
                    supplierUnpaidAmountCell.setCellValue(supplierSettlementDetail.getSupplierUnpaidAmount().toString());

                //实际单价
                XSSFCell actualUnitPriceCell = row.createCell(15);
                actualUnitPriceCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getActualUnitPrice() != null)
                    actualUnitPriceCell.setCellValue(supplierSettlementDetail.getActualUnitPrice().toString());

                //实际数量
                XSSFCell actualQuantityCell = row.createCell(16);
                actualQuantityCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getActualQuantity() != null)
                    actualQuantityCell.setCellValue(supplierSettlementDetail.getActualQuantity().toString());

                //其他费用
                XSSFCell otherExpensesCell = row.createCell(17);
                otherExpensesCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getOtherExpenses() != null)
                    otherExpensesCell.setCellValue(supplierSettlementDetail.getOtherExpenses().toString());

                //实际总成本
                XSSFCell actualTotalCostCell = row.createCell(18);
                actualTotalCostCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getActualTotalCost() != null)
                    actualTotalCostCell.setCellValue(supplierSettlementDetail.getActualTotalCost().toString());

                //实际时长
                XSSFCell actualTimeLengthCell = row.createCell(19);
                actualTimeLengthCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getActualTimeLength() != null)
                    actualTimeLengthCell.setCellValue(supplierSettlementDetail.getActualTimeLength().toString());

                //支付状态
                XSSFCell paymentStateCell = row.createCell(20);
                paymentStateCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getPaymentStateName() != null)
                    paymentStateCell.setCellValue(supplierSettlementDetail.getPaymentStateName().toString());


                //发票类型名称
                XSSFCell invoiceTypeNameCell = row.createCell(21);
                invoiceTypeNameCell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(supplierSettlementDetail.getInvoiceTypeName()))
                    invoiceTypeNameCell.setCellValue(supplierSettlementDetail.getInvoiceTypeName());

                //税点
                XSSFCell taxPointCell = row.createCell(22);
                taxPointCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getTaxPoint() != null)
                    taxPointCell.setCellValue(supplierSettlementDetail.getTaxPoint());

                //拍摄素材
                XSSFCell shootSourceMaterialCell = row.createCell(23);
                shootSourceMaterialCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getShootSourceMaterial() != null)
                    shootSourceMaterialCell.setCellValue(supplierSettlementDetail.getShootSourceMaterial() ? "是" : "否");

                //工程归档
                XSSFCell projectFilingCell = row.createCell(24);
                projectFilingCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getProjectFiling() != null)
                    projectFilingCell.setCellValue(supplierSettlementDetail.getProjectFiling() ? "是" : "否");

                //净版归档  update by zcw  修改导出数据错误bug
                XSSFCell pureFilingCell = row.createCell(25);
                pureFilingCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getPureFiling() != null)
                    pureFilingCell.setCellValue(supplierSettlementDetail.getPureFiling() ? "有" : "否");

                //归档日期
                XSSFCell filingDateCell = row.createCell(26);
                filingDateCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getFilingDate() != null)
                    filingDateCell.setCellValue(simpleDateFormat.format(supplierSettlementDetail.getFilingDate()));

                //预估结算日期
                XSSFCell estimatedSettlementDateCell = row.createCell(27);
                estimatedSettlementDateCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getEstimatedSettlementDate() != null)
                    estimatedSettlementDateCell.setCellValue(simpleDateFormat.format(supplierSettlementDetail.getEstimatedSettlementDate()));

                //预估结算日期
                XSSFCell settlementDateCell = row.createCell(28);
                settlementDateCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getSettlementDate() != null)
                    settlementDateCell.setCellValue(simpleDateFormat.format(supplierSettlementDetail.getSettlementDate()));


                //预估结算日期
                XSSFCell updateTimeCell = row.createCell(29);
                updateTimeCell.setCellType(CellType.STRING);
                if (supplierSettlementDetail.getUpdateTime() != null)
                    updateTimeCell.setCellValue(simpleDateFormat.format(supplierSettlementDetail.getUpdateTime()));

            }
            String fileName = "supplier_settlement_" + new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()) + ".xlsx";
            response.setContentType("application/octet-stream"); // 响应内容格式
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            ServletOutputStream outputStream = response.getOutputStream();
            xssfWorkbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            xssfWorkbook.close();
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导入 课程-供应商服务 数据
     *
     * @param file
     * @return
     */
    @Override
    public Result insertByImportSupplierSettlementDetail(MultipartFile file) {
        if (file.isEmpty()) {
            return Result.failure("文件为空");
        }
        try {
            //根据路径获取这个操作excel的实例
            XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
            //根据页面index 获取sheet页
            XSSFSheet sheet = wb.getSheetAt(0);
            //获取标题行
            XSSFRow titleRow = sheet.getRow(0);
            String[] titleRowArray = new String[]{"服务ID", "实际单价", "实际数量", "其他费用", "实际总成本", "实际时长", "支付状态", "发票类型", "税点", "拍摄素材", "工程归档", "净版归档", "归档时间", "结款日期"};
            for (int i = 0; i < titleRowArray.length; i++) {
                if (!titleRowArray[i].equals(titleRow.getCell(i).toString()))
                    return Result.failure("模板标题行已被修改，请勿修改合同模板标题行");
            }

            List<SupplierSettlementDetail> supplierSettlementDetailList = new ArrayList<>();
            List<Integer> poCourseSupplierIds = new ArrayList<>();

            XSSFRow row = null;
            //循环sesheet页中数据从第二行开始，第一行是标题
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行数据
                row = sheet.getRow(i);
                if (row.getCell(0) == null || StringUtils.isBlank(row.getCell(0).toString()))
                    return Result.failure("本次导入全部失败，第【" + i + "】条数据的 服务ID 为空");
                //实体对象
                SupplierSettlementDetail supplierSettlementDetail = new SupplierSettlementDetail();
                //旧系统中的服务ID
                Integer serviceId = Double.valueOf(row.getCell(0).toString()).intValue();
                //通过旧系统中的服务ID查询课程-供应商服务数据是否已经导入到系统中
                POCourseSupplierDetail poCourseSupplierDetail = poCourseSupplierDetailService.selectOneByImportServiceId(serviceId);
                if (poCourseSupplierDetail != null) {
                    BeanUtils.copyProperties(poCourseSupplierDetail, supplierSettlementDetail, new String[]{"id", "rowEdit", "state", "updateTime", "createTime"});
                    //旧系统中的服务ID
                    supplierSettlementDetail.setImportServiceId(serviceId);
                    //设置课程-供应商对应的课程ID
                    supplierSettlementDetail.setPoCourseSupplierId(poCourseSupplierDetail.getId());
                    poCourseSupplierIds.add(poCourseSupplierDetail.getId());
                } else {
                    return Result.failure("本次导入全部失败，第【" + i + "】条数据，在服务列表中中找不到该服务,请确保课程对应的供应商服务以及导入到系统中");
                }

                //实际单价
                BigDecimal actualUnitPrice = row.getCell(1) == null || StringUtils.isBlank(row.getCell(1).toString()) ? null : BigDecimal.valueOf(Double.valueOf(row.getCell(1).toString()));
                supplierSettlementDetail.setActualUnitPrice(actualUnitPrice);

                //实际数量
                BigDecimal actualQuantity = row.getCell(2) == null || StringUtils.isBlank(row.getCell(2).toString()) ? null : BigDecimal.valueOf(Double.valueOf(row.getCell(2).toString()));
                supplierSettlementDetail.setActualQuantity(actualQuantity);

                //其他费用
                BigDecimal otherExpenses = row.getCell(3) == null || StringUtils.isBlank(row.getCell(3).toString()) ? null : BigDecimal.valueOf(Double.valueOf(row.getCell(3).toString()));
                supplierSettlementDetail.setOtherExpenses(otherExpenses);


                //实际总成本
                BigDecimal actualTotalCost = row.getCell(4) == null || StringUtils.isBlank(row.getCell(4).toString()) ? null : BigDecimal.valueOf(Double.valueOf(row.getCell(4).toString()));
                supplierSettlementDetail.setActualTotalCost(actualTotalCost);

                //实际时长
                String actualTimeLength = row.getCell(5) == null || StringUtils.isBlank(row.getCell(5).toString()) ? null : row.getCell(5).toString();
                supplierSettlementDetail.setActualTimeLength(actualTimeLength);

                //支付状态
                Integer paymentStateId = null;
                String paymentStateName = row.getCell(6) == null || StringUtils.isBlank(row.getCell(6).toString()) ? null : row.getCell(6).toString();
                if (paymentStateName != null) {
                    if (paymentStateName.contains("已结清")) {
                        paymentStateId = 85;
                        paymentStateName = "已结清";
                    }
                    if (paymentStateName.contains("未结清")) {
                        paymentStateId = 86;
                        paymentStateName = "未结清";
                    }
                    if (paymentStateName.contains("未支付")) {
                        paymentStateId = 87;
                        paymentStateName = "未支付";
                    }
                }
                supplierSettlementDetail.setPaymentStateId(paymentStateId);
                supplierSettlementDetail.setPaymentStateName(paymentStateName);


                //发票类型
                Integer invoiceTypeId = null;
                String invoiceTypeName = row.getCell(7) == null || StringUtils.isBlank(row.getCell(7).toString()) ? null : row.getCell(7).toString();
                if (invoiceTypeName != null) {
                    if (invoiceTypeName.contains("增值税普票")) {
                        invoiceTypeId = 81;
                        invoiceTypeName = "增值税普票";
                    }
                    if (invoiceTypeName.contains("增值税专票")) {
                        invoiceTypeId = 82;
                        invoiceTypeName = "增值税专票";
                    }
                    if (invoiceTypeName.contains("劳务结算")) {
                        invoiceTypeId = 83;
                        invoiceTypeName = "劳务结算";
                    }
                    if (invoiceTypeName.contains("无法提供")) {
                        invoiceTypeId = 84;
                        invoiceTypeName = "无法提供";
                    }
                }
                supplierSettlementDetail.setInvoiceTypeId(invoiceTypeId);
                supplierSettlementDetail.setInvoiceTypeName(invoiceTypeName);

                //税点
                String taxPoint = row.getCell(8) == null || StringUtils.isBlank(row.getCell(8).toString()) ? null : row.getCell(8).toString();
                supplierSettlementDetail.setTaxPoint(taxPoint);

                //拍摄素材
                Boolean shootSourceMaterial = row.getCell(9) == null || StringUtils.isBlank(row.getCell(9).toString()) ? null : row.getCell(9).toString().trim().contains("有") ? true : false;
                supplierSettlementDetail.setShootSourceMaterial(shootSourceMaterial);

                //工程归档
                Boolean projectFiling = row.getCell(10) == null || StringUtils.isBlank(row.getCell(10).toString()) ? null : row.getCell(10).toString().trim().contains("有") ? true : false;
                supplierSettlementDetail.setProjectFiling(projectFiling);

                //净版归档
                Boolean pureFiling = row.getCell(11) == null || StringUtils.isBlank(row.getCell(11).toString()) ? null : row.getCell(11).toString().trim().contains("有") ? true : false;
                supplierSettlementDetail.setPureFiling(pureFiling);

                //归档日期
                Date filingDate = row.getCell(12) == null || StringUtils.isBlank(row.getCell(12).toString()) ? null : row.getCell(12).getDateCellValue();
                supplierSettlementDetail.setFilingDate(filingDate);

                //结款日期
                Date settlementDate = row.getCell(13) == null || StringUtils.isBlank(row.getCell(13).toString()) ? null : row.getCell(13).getDateCellValue();
                supplierSettlementDetail.setSettlementDate(settlementDate);
                supplierSettlementDetailList.add(supplierSettlementDetail);
            }

            if (supplierSettlementDetailList.size() > 0) {
                //插入 课程供应商服务对应的结算清单数据
                supplierSettlementDetailDao.insertBatchList(supplierSettlementDetailList);
                //更新导入服务数据状态为 已生成结算清单
                poCourseSupplierDetailDao.updateBatchList(poCourseSupplierIds);
                return Result.success();
            } else {
                return Result.failure("表格中没有数据，导入失败");
            }
        } catch (IOException e) {
            log.error(e.getMessage());
            return Result.failure("导入失败，请检查数据格式是否符合模板要求：" + e.getMessage());
        }
    }



    /**
     * 导入结算总表
     *
     * @param ids
     * @return
     */
    @Override
    public Result updateStateById(String ids) {
        String[] items = ids.split(",");
        List<SupplierSettlementDetail> list = new ArrayList<>();
        for (String id : items) {
            list.add(SupplierSettlementDetail.builder().id(Integer.valueOf(id)).state(GlobalConstants.SUPPLIER_SETTLEMENT_STATE_INIT).build());
        }
        if (supplierSettlementDetailDao.updateStateById(list) > 0) {
            return Result.success();
        } else {
            return Result.failure("操作失败");
        }
    }

    @Override
    public Result updateStateById4Export(String ids, String state) {
        List<Integer> ids4Update = new ArrayList<Integer>();
        for (String item:ids.split(",")){
            ids4Update.add(new Integer(item));
        }
        int updateFlag = supplierSettlementDetailDao.updateStateById4Export(ids4Update, state);
        if(updateFlag > 0){
            return Result.success();
        }else{
            return Result.failure("数据更新失败");
        }
    }


    /**
     * 获取PO 下的 预估总和 已支付总和 未支付总和
     * @return
     */
    @Override
    public Map<String, SupplierPoVo> selectSumCostAndPaidByPo() {
        //1. 查询 p_supplier_settlement_detail 按照po单号 求和(预估 未支付 已支付)
        List<SupplierPoVo> voList = supplierSettlementDetailDao.selectSumCostAndPaidByPo();
        //2. 转换 将 List<SupplierPoVo> --> Map<po,SupplierPoVo>
        Map<String,SupplierPoVo> map = new HashMap<String,SupplierPoVo>(voList.size());
        for(SupplierPoVo vo : voList){
            map.put(vo.getPo(),vo);
        }
        return map;
    }

    @Override
    public Result selectSupplierSettlementDetialByPoCourseSupplierId4Export(String ids) {
        QueryWrapper<SupplierSettlementDetail> supplierSettlementDetail = new QueryWrapper<SupplierSettlementDetail>();
        supplierSettlementDetail.in("id", Arrays.asList(ids.split(",")));
        List<SupplierSettlementDetail> ssds = supplierSettlementDetailDao.selectList(supplierSettlementDetail);

        List<Integer> poIds = new ArrayList<Integer>();
        for(SupplierSettlementDetail item : ssds){
            poIds.add(item.getPoId());
        }

        QueryWrapper<PO>  posQueryWrapper= new QueryWrapper<PO>();
        posQueryWrapper.in("id", poIds);
        List<PO> pos = poDao.selectList(posQueryWrapper);
        List<SupplierSettlementPoVo> sspvs = new ArrayList<SupplierSettlementPoVo>();
        for (SupplierSettlementDetail item : ssds){
            SupplierSettlementPoVo sspv = new SupplierSettlementPoVo();
            sspv.setSupplierSettlementDetail(item);
            for(PO po : pos){
                Integer poId = po.getId();
                if(poId.equals(item.getPoId())){
                    sspv.setContractNo(po.getContractNo());
                }
            }
            SimpleDateFormat formatter= new SimpleDateFormat("yyyyMMdd 'at' HH:mm:ss z");
            Date date = new Date(System.currentTimeMillis());
            String settlementNum = formatter.format(date).substring(0,8)+"-JS-"+ (int)(Math.random() * 900 + 100) ;
            sspv.setSettlementNum(settlementNum);
            sspvs.add(sspv);
        }
        return Result.success(sspvs);
    }
}
